﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.Common
{
    public class DataBaseHelper_41
    {
        static string testConnect = @"Data Source={0},1433;Initial Catalog={1};User ID={2};Password ={3};Persist Security Info=True;Connection Timeout=30";
        #region 或取connection
        public static SqlConnection getSqlConnection()
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn.ConnectionString = @"data source=172.23.134.41;initial catalog=LamexMES_TransferTP;user id=sa;password=Mtp@0417%;Connect Timeout=5;";
                conn.Open();                
            }
            catch
            {
                throw new Exception("无法连接数据库服务器。");
            }
            return conn;
        }
        #endregion

        #region 根据sqlstr执行增、删、改
        public static int GetNonQueryEffectedRow(string sqlstr)
        {
            SqlConnection conn = getSqlConnection();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlstr, conn);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }//根据sqlstr执行增、删、改
        #endregion

        #region 根据sqlstr获取DataTable
        public static DataTable GetDataTableBySql(string sqlstr)
        {
            SqlConnection conn = getSqlConnection();
            try
            {
                SqlDataAdapter sda = new SqlDataAdapter(sqlstr, conn);
                DataSet ds = new DataSet();
                conn.Close();
                sda.Fill(ds);
                return ds.Tables[0];
                
                
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

        #endregion
        public static string getSqlConnection2()
        {
            return @"data source=172.23.134.41;initial catalog=LamexMES_TransferTP;user id=sa;password=Mtp@0417%;Connect Timeout=5;";
        }
       #region batch insert data
        public static bool ExecuteInsertBulkData(string tempSql, DataSet ds, string sql)
        {
            
            string sConn = getSqlConnection2();
            SqlConnection con = new SqlConnection(sConn);
            con.Open();
            using (SqlCommand cmd = con.CreateCommand()) //创建命令对象  com = new SqlCommand();
            {

                cmd.CommandText = tempSql;
                int m = cmd.ExecuteNonQuery();
                string name = "";

                foreach (DataTable dt in ds.Tables)
                {
                    name = dt.TableName;
                    string dtTempName = "#" + dt.TableName; //构建需要插入数据的临时表名称
                    SqlBulkCopy sqlBC = new SqlBulkCopy(con);//启用批量复制；
                    sqlBC.BulkCopyTimeout = 5000;
                    sqlBC.DestinationTableName = dtTempName;
                    sqlBC.NotifyAfter = 100;
                    sqlBC.WriteToServer(dt);

                }
                //************************开始事务操作数据*****************************//
                SqlCommand command = con.CreateCommand();
                SqlTransaction transaction;
                transaction = con.BeginTransaction("SampleTransaction");//开始事务
                command.Connection = con;
                command.Transaction = transaction;
                try
                {
                    string tranSql = "set identity_insert " + name + " on  ;" + sql + " ; set identity_insert " + name + " off  ;";
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                    transaction.Commit();//提交事务
                    con.Close();
                    return true;
                }
                catch (System.Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }
                finally
                {
                    con.Close();
                }

            }

        }


        #endregion

       # region excutive sql
        public static int ExecuteNonQuery(string sql)
        {
            try
            {

                string sConn = getSqlConnection2();
                //string sConn = @"data source=172.23.134.41;initial catalog=LamexMES_TransferTP;user id=sa;password=Mtp@0417%;Connect Timeout=5;";;
                SqlConnection con = new SqlConnection(sConn);
                con.Open();
                using (SqlCommand cmd = con.CreateCommand()) //创建命令对象  com = new SqlCommand();
                {
                    cmd.CommandText = sql;
                    int m = cmd.ExecuteNonQuery();
                    CloseConnection(con);
                    return m;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        private static void CloseConnection(SqlConnection connection)
        {
            if (connection == null) return;
            if (connection.State != ConnectionState.Closed) connection.Close();
        }
        #endregion 

        public static DataTable GetDataTableBySql2(string sql)
        {
            try
            {
                
                string sConn = getSqlConnection2();
                SqlConnection con = new SqlConnection(sConn);
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sql;
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    CloseConnection(con);
                    return ds.Tables[0];
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        //get columnheader name
        public static DataTable GetSysColunmNameList(string tableName)
        {
            string sql = @"SELECT  
                     d.name as table_name,  
                     a.name column_name,
                     case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
                      SELECT name FROM sysindexes WHERE indid in(
                       SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
                       ))) then 'key' else '' end as keyname, 
                    b.name column_type,
                    isnull(g.[value],'') column_remark
                    FROM syscolumns a 
                    left join systypes b on a.xtype=b.xusertype 
                    inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
                    left join syscomments e on a.cdefault=e.id 
                    left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
                    left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 
                    where d.name='" + tableName + "' order by a.id,a.colorder ";
            return GetDataTableBySql(sql);
        }

        public static bool ExecuteInsertBulkData(string tempSql, DataSet ds, string sql, ref string errMsg)
        {
            //string[] arr = sql.Split(';');
            //string tempSql = "";
            //foreach(string s in arr)
            //{
            //    tempSql += " where 1!=1;";
            //}

            SqlConnection con = getSqlConnection();
 
            using (SqlCommand cmd = con.CreateCommand()) //创建命令对象  com = new SqlCommand();
            {

                cmd.CommandText = tempSql;
                int m = cmd.ExecuteNonQuery();

                foreach (DataTable dt in ds.Tables)
                {
                    string dtTempName = "#" + dt.TableName; //构建需要插入数据的临时表名称
                    SqlBulkCopy sqlBC = new SqlBulkCopy(con);//启用批量复制；
                    sqlBC.BulkCopyTimeout = 5000;
                    sqlBC.DestinationTableName = dtTempName;
                    sqlBC.NotifyAfter = 100;
                    sqlBC.WriteToServer(dt);

                }
                //************************开始事务操作数据*****************************//
                SqlCommand command = con.CreateCommand();
                SqlTransaction transaction;
                transaction = con.BeginTransaction("SampleTransaction");//开始事务
                command.Connection = con;
                command.Transaction = transaction;
                try
                {
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                    transaction.Commit();//commit
                    con.Close();
                    return true;
                }
                catch (System.Exception ex)
                {
                    transaction.Rollback();
                    errMsg = ex.Message;
                    return false;
                }
                finally
                {
                    con.Close();
                }

            }

        }
    }
}
